import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.examples.CellTypes;

public class ExcelReader {
	static HashMap<Integer, String> columns = null;

	public HashMap<String, ArrayList<EANObject>> readXLSX(String fileName) throws IOException,
			InterruptedException {
		
		InputStream ExcelFileToRead = new FileInputStream(fileName);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell;

		Iterator rows = sheet.rowIterator();

		HashMap<String , ArrayList<EANObject>> objects = new HashMap<String , ArrayList<EANObject>>();
		boolean columnRow = true;
		while (rows.hasNext()) {
			EANObject newObject = new EANObject();
			row = (XSSFRow) rows.next();
			
			if(columnRow){
				columns = getColumnListing(row);
				columnRow = false;
			}
			else{
				Iterator cells = row.cellIterator();
				while (cells.hasNext()) {
					cell = (XSSFCell) cells.next();
					setValue(cell, newObject, columns,evaluator);
				}
				insertObject(newObject, objects);
			}
		}
		
		return objects;
	}
	
	
	public HashMap<String, ArrayList<EANObject>> readXLS(String fileName) throws IOException{
		InputStream ExcelFileToRead = new FileInputStream(fileName);
		HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
		 
		HSSFSheet sheet=wb.getSheetAt(0);
		HSSFRow row;
		HSSFCell cell;
 

		Iterator rows = sheet.rowIterator();
		
		HashMap<String , ArrayList<EANObject>> objects = new HashMap<String , ArrayList<EANObject>>();
		HashMap<Integer, String> columns = null;
		boolean columnRow = true;
		while (rows.hasNext()) {
			EANObject newObject = new EANObject();
			row = (HSSFRow) rows.next();
			
			if(columnRow){
				columns = getColumnListingXLS(row);
				columnRow = false;
			}
			else{
				Iterator cells = row.cellIterator();
				while (cells.hasNext()) {
					cell = (HSSFCell) cells.next();
					setValueXLS(cell, newObject, columns);
				}
				insertObject(newObject, objects);
			}
		}
		
		return objects;
	}

	private void insertObject(EANObject newObject, HashMap<String, ArrayList<EANObject>> objects) {
		ArrayList<EANObject> checkList = objects.get(newObject.getEan());
		
		if(checkList == null){
			checkList = new ArrayList<EANObject>();
		}
		
		checkList.add(newObject);
		objects.put(newObject.getEan(), checkList);
		
	}

	
	private static void setValue(XSSFCell cell, EANObject newObject, HashMap<Integer, String> columns, FormulaEvaluator evaluator) {
		
		if(columns.get(cell.getColumnIndex()).equals("articleNumber")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setArticleNumber(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("title")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setTitle(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("brand")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setBrand(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("description")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setDescription(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("weight")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			try{
				newObject.setWeight(Double.parseDouble(cell.getStringCellValue()));

			}
			catch(NumberFormatException e){
				System.out.println("error  : " + cell);
				newObject.setWeight(0.0);
			}
		}
		else if(columns.get(cell.getColumnIndex()).equals("ean")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setEan(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Bolcom prijs")){ // special cell. save it's formula as well.
			CellValue cellValue = evaluator.evaluate(cell);
			cell.setCellType(Cell.CELL_TYPE_FORMULA);
			newObject.setBolPriceFormula(cell.getCellFormula());
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setBolPrice( (float) cellValue.getNumberValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("stock")){
			cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
			newObject.setInStock(cell.getBooleanCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("price")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setPrice(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("recommendedPrice")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setReconnmendedPrice(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Aanbevolenprijs")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setAanbevolenPrijs(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Prijsinclusiefwinstmargeenverzendkosten")){
			CellValue cellValue = evaluator.evaluate(cell);
			cell.setCellType(Cell.CELL_TYPE_FORMULA);
			newObject.setPriceIncludedFormula(cell.getCellFormula());
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setPriceIncluded(cellValue.getNumberValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("AskingPrice")){
			CellValue cellValue = evaluator.evaluate(cell);
			cell.setCellType(Cell.CELL_TYPE_FORMULA);
			newObject.setAskingPriceFormula(cell.getCellFormula());
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setAskingPrice(cellValue.getNumberValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Leverancier")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setSupplier(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("TitelNL")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setTitleNl(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("categoryName1")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setCategoryName1(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("categoryName2")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setCategoryName2(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("categoryName3")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setCategoryName3(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("GoogleCategory")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setGoogleCatagory(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image1")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage1(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image2")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage2(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image3")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage3(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image4")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage4(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image5")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage5(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image6")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage6(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image7")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage7(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image8")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage8(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image9")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage9(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image10")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage10(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("delivery")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setDelivery(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("warranty")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setWarrenty(cell.getStringCellValue());
		}
	}
	
	private static void setValueXLS(HSSFCell cell, EANObject newObject, HashMap<Integer, String> columns) {
		if(columns.get(cell.getColumnIndex()).equals("articleNumber")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setArticleNumber(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("title")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setTitle(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("brand")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setBrand(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("description")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setDescription(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("weight")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setWeight(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("ean")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setEan(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Bolcom prijs")){ // special cell. save it's formula as well.
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setBolPrice( (float) cell.getNumericCellValue());
			newObject.setBolPriceFormula(cell.getCellFormula());
		}
		else if(columns.get(cell.getColumnIndex()).equals("stock")){
			cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
			newObject.setInStock(cell.getBooleanCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("price")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setPrice(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("recommendedPrice")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setReconnmendedPrice(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Aanbevolenprijs")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setAanbevolenPrijs(cell.getNumericCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Prijsinclusiefwinstmargeenverzendkosten")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setPriceIncluded(cell.getNumericCellValue());
			cell.setCellType(Cell.CELL_TYPE_FORMULA);
			newObject.setPriceIncludedFormula(cell.getCellFormula());
		}
		else if(columns.get(cell.getColumnIndex()).equals("AskingPrice")){
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			newObject.setAskingPrice(cell.getNumericCellValue());
			newObject.setAskingPriceFormula(cell.getCellFormula());
		}
		else if(columns.get(cell.getColumnIndex()).equals("Leverancier")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setSupplier(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("TitelNL")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setTitleNl(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("categoryName1")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setCategoryName1(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("categoryName2")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setCategoryName2(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("categoryName3")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setCategoryName3(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("GoogleCategory")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setGoogleCatagory(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image1")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage1(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image2")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage2(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image3")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage3(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image4")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage4(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image5")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage5(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image6")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage6(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image7")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage7(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image8")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage8(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image9")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage9(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("image10")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setImage10(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("delivery")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setDelivery(cell.getStringCellValue());
		}
		else if(columns.get(cell.getColumnIndex()).equals("warranty")){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			newObject.setWarrenty(cell.getStringCellValue());
		}
	}

	private static HashMap<Integer, String> getColumnListing(XSSFRow row) {
		HashMap<Integer, String> columns = new HashMap<Integer, String>();
		XSSFCell cell;
		
		Iterator cells = row.cellIterator();
		while (cells.hasNext()) {
			cell = (XSSFCell) cells.next();
			cell.setCellType(Cell.CELL_TYPE_STRING);
			columns.put(cell.getColumnIndex(), cell.getStringCellValue());
		}
		
		return columns;
	}
	
	private static HashMap<Integer, String> getColumnListingXLS(HSSFRow row) {
		HashMap<Integer, String> columns = new HashMap<Integer, String>();
		HSSFCell cell;
		
		Iterator cells = row.cellIterator();
		while (cells.hasNext()) {
			cell = (HSSFCell) cells.next();
			cell.setCellType(Cell.CELL_TYPE_STRING);
			columns.put(cell.getColumnIndex(), cell.getStringCellValue());
		}
		
		return columns;
	}
	
	public void createNewListFile(HashMap<String, ArrayList<EANObject>> newItemList, String fileName) throws IOException {
		 
		String sheetName = "Sheet1";//name of sheet
		 
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(sheetName) ;
		
		int columnHeaderCounter = 0;
		XSSFRow headerRow = sheet.createRow(0);
		for(String header : this.columns.values()){
			XSSFCell cell = headerRow.createCell(columnHeaderCounter);
			cell.setCellValue(header);
			columnHeaderCounter++;
		}
		 
		int rowCounter = 1;
		for(ArrayList<EANObject> list : newItemList.values()){
			for(EANObject newEANObject: list){
				XSSFRow row = sheet.createRow(rowCounter);
				insertRow(row,newEANObject,rowCounter,wb);
				rowCounter++;
			}
		}
		 
		FileOutputStream fileOut = new FileOutputStream(fileName);
		 
		//write this workbook to an Outputstream.
		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}


	private static void insertRow(XSSFRow row, EANObject newEANObject, int rowCounter, XSSFWorkbook wb) {
		
		for(int cellCounter = 0; cellCounter < columns.size() ; cellCounter++){
			if(columns.get(cellCounter).equals("articleNumber")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getArticleNumber());
			}
			else if(columns.get(cellCounter).equals("title")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getTitle());
			}
			else if(columns.get(cellCounter).equals("brand")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getBrand());
			}
			else if(columns.get(cellCounter).equals("description")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getDescription());
			}
			else if(columns.get(cellCounter).equals("weight")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getWeight());
			}
			else if(columns.get(cellCounter).equals("ean")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getEan());
			}
			else if(columns.get(cellCounter).equals("Bolcom prijs")){
				 XSSFCellStyle cs = wb.createCellStyle();
				 cs.setDataFormat((short)2); 
				 double numer1 = 1.15;
				String newFormula = createFormula(newEANObject.getBolPriceFormula(), "*", rowCounter, "SUM(L2*"+numer1+")+2");
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellFormula(newFormula);
				cell.setCellStyle(cs);
			}
			else if(columns.get(cellCounter).equals("stock")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.isInStock());
			}
			else if(columns.get(cellCounter).equals("price")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getPrice());
			}
			else if(columns.get(cellCounter).equals("recommendedPrice")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getReconnmendedPrice());
			}
			else if(columns.get(cellCounter).equals("Aanbevolenprijs")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getAanbevolenPrijs());
			}
			else if(columns.get(cellCounter).equals("Prijsinclusiefwinstmargeenverzendkosten")){
				XSSFCellStyle cs = wb.createCellStyle();
				 cs.setDataFormat((short)2);
				double numer1 = 1.21;
				double numer2 = 1.15;
				String newFormula = createFormula(newEANObject.getPriceIncludedFormula(), "*", rowCounter, "SUM(I2*"+ numer1 +")*"+ numer2 + "+7");
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellFormula(newFormula);
				cell.setCellStyle(cs);
			}
			else if(columns.get(cellCounter).equals("AskingPrice")){
				XSSFCellStyle cs = wb.createCellStyle();
				 cs.setDataFormat((short)2);
				double number = 6.75;
				String newFormula = createFormula(newEANObject.getAskingPriceFormula(), "-", rowCounter, "SUM(L2-"+ number +")");
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellFormula(newFormula);
				cell.setCellStyle(cs);
			}
			else if(columns.get(cellCounter).equals("Leverancier")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getSupplier());
			}
			else if(columns.get(cellCounter).equals("TitelNL")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getTitleNl());
			}
			else if(columns.get(cellCounter).equals("categoryName1")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getCategoryName1());
			}
			else if(columns.get(cellCounter).equals("categoryName2")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getCategoryName2());
			}
			else if(columns.get(cellCounter).equals("categoryName3")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getCategoryName3());
			}
			else if(columns.get(cellCounter).equals("GoogleCategory")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getGoogleCatagory());
			}
			else if(columns.get(cellCounter).equals("image1")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage1());
			}
			else if(columns.get(cellCounter).equals("image2")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage2());
			}
			else if(columns.get(cellCounter).equals("image3")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage3());
			}
			else if(columns.get(cellCounter).equals("image4")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage4());
			}
			else if(columns.get(cellCounter).equals("image5")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage5());
			}
			else if(columns.get(cellCounter).equals("image6")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage6());
			}
			else if(columns.get(cellCounter).equals("image7")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage7());
			}
			else if(columns.get(cellCounter).equals("image8")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage8());
			}
			else if(columns.get(cellCounter).equals("image9")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage9());
			}
			else if(columns.get(cellCounter).equals("image10")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getImage10());
			}
			else if(columns.get(cellCounter).equals("delivery")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getDelivery());
			}
			else if(columns.get(cellCounter).equals("warranty")){
				XSSFCell cell = row.createCell(cellCounter);
				cell.setCellValue(newEANObject.getWarrenty());
			}
		}
	}


	private static String createFormula(String askingPriceFormula, String prefix, int counter, String defaultFormula) {
		if(askingPriceFormula == null){
			askingPriceFormula = defaultFormula;
		}
		
		String left = askingPriceFormula.substring(0, 5);
		
		String right = null;
		if(askingPriceFormula.substring(5, 6).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(5);
		}
		else if(askingPriceFormula.substring(6, 7).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(6);
		}
		else if(askingPriceFormula.substring(7, 8).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(7);
		}
		else if(askingPriceFormula.substring(8, 9).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(8);
		}
		else if(askingPriceFormula.substring(9, 10).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(9);
		}
		else if(askingPriceFormula.substring(10, 11).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(10);
		}
		else if(askingPriceFormula.substring(11, 12).equalsIgnoreCase(prefix)){
			right = askingPriceFormula.substring(11);
		}
		return left + (counter+1) + right;
	}
	
	public void writeNewListFiles(ArrayList<String> priceChangedList, HashMap<String, ArrayList<EANObject>> mainList) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();

		//create headers
		XSSFSheet bolcomSheet = wb.createSheet("bolcom") ;
		XSSFRow headerRow = bolcomSheet.createRow(0);
		XSSFCell cell = headerRow.createCell(0);
		cell.setCellValue("ean");
		XSSFCell cell2 = headerRow.createCell(1);
		cell2.setCellValue("Bolcom price");
		
		XSSFSheet askingPriceSheet = wb.createSheet("askingprice") ;
		XSSFRow askingPriceheaderRow = askingPriceSheet.createRow(0);
		XSSFCell askingPricecell = askingPriceheaderRow.createCell(0);
		askingPricecell.setCellValue("ean");
		XSSFCell askingPricecell2 = askingPriceheaderRow.createCell(1);
		askingPricecell2.setCellValue("Askingprice");
		 
		int rowCounter = 1;
		for(String key : priceChangedList){
			for(EANObject newEANObject: mainList.get(key)){
				XSSFRow row = bolcomSheet.createRow(rowCounter);
				XSSFCell cellEAN = row.createCell(0);
				cellEAN.setCellValue(newEANObject.getEan());
				XSSFCell cellBolcom = row.createCell(1);
				cellBolcom.setCellValue(newEANObject.getBolPrice());
				XSSFCellStyle cs = wb.createCellStyle();
				cs.setDataFormat((short)8);
				cellBolcom.setCellStyle(cs);
				
				XSSFRow askingPricerow = askingPriceSheet.createRow(rowCounter);
				XSSFCell askingPricecellEAN = askingPricerow.createCell(0);
				askingPricecellEAN.setCellValue(newEANObject.getEan());
				XSSFCell cellAskingprice = askingPricerow.createCell(1);
				cellAskingprice.setCellValue(newEANObject.getPriceIncluded() - 6.75);
				cellAskingprice.setCellStyle(cs);
				
				rowCounter++;
			}
		}
		 
		FileOutputStream fileOut = new FileOutputStream("prijsLijsten.xlsx");
		 
		//write this workbook to an Outputstream.
		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}
}
